﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class AdvertisementManagement
    {
        public static DataTable listAdvertisementPeriod()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listAdvertisementPeriod";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable listAdvertisementPictureSize()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listAdvertisementPictureSize";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;

        }

        public static void insertAdvertisement(string username, string img, int pictureSize, int period, decimal totalprice)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "insertAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@memberName", SqlDbType.VarChar, 50));
                cmd.Parameters["@memberName"].Value = username;

                cmd.Parameters.Add(new SqlParameter("@imagePath", SqlDbType.VarChar, 50));
                cmd.Parameters["@imagePath"].Value = img;

                cmd.Parameters.Add(new SqlParameter("@pictureSize", SqlDbType.Int));
                cmd.Parameters["@pictureSize"].Value = pictureSize;

                cmd.Parameters.Add(new SqlParameter("@period", SqlDbType.Int));
                cmd.Parameters["@period"].Value = period;

                cmd.Parameters.Add(new SqlParameter("@totalprice", SqlDbType.Decimal));
                cmd.Parameters["@totalprice"].Value = totalprice;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }
        }

        public static DataTable listVendorAdvertisement(string size)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listVendorAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@PicSize", SqlDbType.VarChar, 50));
                cmd.Parameters["@PicSize"].Value = size;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable listUniversaleAdvertisement(string position, string picSize)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listUniversaleAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Position", SqlDbType.VarChar, 50));
                cmd.Parameters["@Position"].Value = position;

                cmd.Parameters.Add(new SqlParameter("@PicSize", SqlDbType.VarChar, 50));
                cmd.Parameters["@PicSize"].Value = picSize;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }
        
    }

}
